from model.University import University
from model.UniversityMajorPlan import UniversityMajorPlan
from model.UniversityMajorRank import UniversityMajorRank
from model.UniversityMajorRankMean import UniversityMajorRankMean
from util import mysql_utils

pool = mysql_utils.get_mysql_pool()


def query_config_by_assign():
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT year, value FROM config WHERE `key`='assign' ORDER BY year DESC LIMIT 3"
            # print(sql_query)
            cursor.execute(sql_query)
            result = cursor.fetchall()
            # 转换查询结果为字典列表
            if result is not None:
                result_dicts = [{'year': row[0], 'value': row[1]} for row in result]
                return result_dicts
            else:
                return None
    except Exception as e:
        print(f'查询config异常：{e}')
        return None
    finally:
        conn.close()


# 根据分数获取省内一分一档表获取排名
def one_by_score(score, year, type):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT `rank`, `total_num` FROM one_score_rank_shengnei WHERE `year`={year} AND `type`={type} AND `score`={score}  LIMIT 1"
            # print(sql_query)
            cursor.execute(sql_query)
            result = cursor.fetchone()
            # 将查询结果转换为字典
            if result is not None:
                result_dict = dict(zip(['rank', 'total_num'], result))
                return result_dict
            else:
                return None
    except Exception as e:
        print(f'查询省内一分一档异常：{e}')
        return None
    finally:
        conn.close()


# 根据分数获取全国一分一档表获取排名
def one_by_score_2(score, year, type):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT `rank`, `total_num` FROM one_score_rank WHERE `year`={year} AND `type`={type} AND `score`={score}  LIMIT 1"
            # print(sql_query)
            cursor.execute(sql_query)
            result = cursor.fetchone()
            # 将查询结果转换为字典
            if result is not None:
                result_dict = dict(zip(['rank', 'total_num'], result))
                return result_dict
            else:
                return None
    except Exception as e:
        print(f'查询省内一分一档异常：{e}')
        return None
    finally:
        conn.close()


# 计算专业近三年录取最低分的平均值
def mean_lowest_score(data):
    i = 0
    total_rank = 0
    for index, row in enumerate(data):
        if index < 3 and row['lowest_score'] > 0:
            i = i + 1
            total_rank = total_rank + row['lowest_score']
    mean_rank = 0
    if i > 0:
        mean_rank = round(total_rank / i)
    return mean_rank


# 计算专业近三年录取最低分的排名的平均值
def mean_lowest_rank(data):
    i = 0
    total_rank = 0
    for index, row in enumerate(data):
        if index < 3 and row['lowest_rank'] > 0:
            i = i + 1
            total_rank = total_rank + row['lowest_rank']
    mean_rank = 0
    if i > 0:
        mean_rank = round(total_rank / i)
    return mean_rank


# 计算专业近三年录取平均分的平均值
def mean_mean_score(data):
    i = 0
    total_rank = 0
    for index, row in enumerate(data):
        if index < 3 and row['mean_score'] > 0:
            i = i + 1
            total_rank = total_rank + row['mean_score']
    mean_rank = 0
    if i > 0:
        mean_rank = round(total_rank / i)
    return mean_rank


# 计算专业近三年录取平均排名的平均值
def mean_mean_rank(data):
    i = 0
    total_rank = 0
    for index, row in enumerate(data):
        if index < 3 and row['mean_rank'] > 0:
            i = i + 1
            total_rank = total_rank + row['mean_rank']
    mean_rank = 0
    if i > 0:
        mean_rank = round(total_rank / i)
    return mean_rank


# 计算专业近三年录取最低排名的加权平均值（排名以全国一分一档表为准）
def assign_mean_rank(data):
    # 加权系数
    assign_temp = query_config_by_assign()
    assign = dict()
    if assign_temp:
        for index, val in enumerate(assign_temp):
            assign[val['year']] = val['value']
    i = 0
    total_rank = 0
    # 数据小于三年，不加权
    if len(data) < 3:
        for index, row in enumerate(data):
            if row['lowest_rank'] > 0:
                i = i + 1
                total_rank = total_rank + row['lowest_rank']
    else:
        for index, row in enumerate(data):
            if index < 3 and row['lowest_rank'] > 0:
                i = i + 1
                total_rank = total_rank + row['lowest_rank'] * assign[row['year']]
    ass_mean_rank = 999999  # 默认设置加权平均排名
    if i > 0:
        ass_mean_rank = round(total_rank / i)
    return ass_mean_rank


# 计算专业近三年加权录取最低排名的平均值（排名以省内排名为准）
def assign_lowest_rank(data, type):
    # 加权系数
    assign_temp = query_config_by_assign()
    assign = dict()
    if assign_temp:
        for index, val in enumerate(assign_temp):
            assign[val['year']] = val['value']
    i = 0
    total_rank = 0
    if len(data) < 3:
        for index, row in enumerate(data):
            if row['lowest_score'] > 0:
                # 根据分数获取省内一分一档表获取排名
                one_score_rank = one_by_score(row['lowest_score'], row['year'], type)
                if one_score_rank:
                    total_rank = total_rank + one_score_rank['rank']
                    i = i + 1
    else:
        for index, row in enumerate(data):
            if index < 3 and row['lowest_score'] > 0:
                # 根据分数获取省内一分一档表获取排名
                one_score_rank = one_by_score(row['lowest_score'], row['year'], type)
                if one_score_rank:
                    total_rank = total_rank + one_score_rank['rank'] * assign[row['year']]
                    i = i + 1
    ass_mean_rank = 999999  # 默认设置加权平均排名
    if i > 0:
        ass_mean_rank = round(total_rank / i)
    return ass_mean_rank


# 从university_major_plan找到is_add的数据，重新计算后存入university_major_rank_mean
def to_do_error():
    university = University()
    universityMajorRank = UniversityMajorRank()
    universityMajorPlan = UniversityMajorPlan()
    universityMajorRankMean = UniversityMajorRankMean()
    mean_year = 2010  # 获取三年平均数时候的起始年份
    mean_year_2 = 2022  # 获取三年平均数时候的起始年份
    year = 2023
    data = universityMajorPlan.getListAll3(year, 1)
    total_list = list()
    for index, row in enumerate(data):
        print(row)
        row_temp = dict()
        row_temp['year'] = year
        row_temp['batch'] = row['batch']
        row_temp['type'] = row['type']
        row_temp['code'] = row['ucode']
        row_temp['uname'] = row['uname']
        row_temp['major_code'] = row['major_code']
        row_temp['name'] = row['major']
        row_temp['plan_number'] = row['plan_number']
        row_temp['cut_remarks'] = row['cut_remarks']
        row_temp['province_code'] = row['province_code']
        row_temp['city_code'] = row['city_code']
        row_temp['province'] = row['province']
        row_temp['city'] = row['city']
        # 院校排名
        university_row = university.getOneByCode(row['ucode'])
        if university_row:
            row_temp['university_rank'] = university_row['university_rank']
            row_temp['type_name'] = university_row['type_name']
            row_temp['nature_name'] = university_row['nature_name']
            row_temp['other_type'] = university_row['other_type']
        else:
            row_temp['university_rank'] = 999  # 默认设置院校排名为999
            row_temp['type_name'] = ''
            row_temp['nature_name'] = 0
            row_temp['other_type'] = 0
        # 近三年所有专业录取最低分、录取最低排名、录取平均分、录取平均排名
        major_data_rank = universityMajorRank.getRowsByYearBatch(mean_year, mean_year_2, row['batch'], row['type'],
                                                                 row['ucode'], row['major'])
        # 计算专业近三年录取平均分的平均值、近三年录取最低分平均值、近三年录取最低排名平均值、近三年录取平均排名平均值
        row_temp['mean_lowest_score'] = mean_lowest_score(major_data_rank)
        row_temp['mean_lowest_rank'] = mean_lowest_rank(major_data_rank)
        row_temp['mean_mean_score'] = mean_mean_score(major_data_rank)
        row_temp['mean_mean_rank'] = mean_mean_rank(major_data_rank)
        row_temp['assign_mean_rank'] = assign_mean_rank(major_data_rank)
        row_temp['assign_lowest_rank'] = assign_lowest_rank(major_data_rank, row['type'])
        total_list.append(row_temp)
    universityMajorRankMean.toInsertBatch(total_list)


# 获取近两年高考分数线
def get_control_line_score(year, batch, type, key):
    conn = pool.connection()
    try:
        with conn.cursor() as cursor:
            sql_query = f"SELECT value, year FROM config WHERE `year`>={year} AND `batch`={batch} AND `type`={type} AND `key`='{key}' ORDER BY `year` DESC"
            # print(sql_query)
            cursor.execute(sql_query)
            result = cursor.fetchall()
            # 转换查询结果为字典列表
            if result is not None:
                result_dicts = [{'value': row[0], 'year': row[1]} for row in result]
                return result_dicts
            else:
                return None
    except Exception as e:
        print(f'查询config异常：{e}')
        return None
    finally:
        conn.close()